import mysql.connector
from mysql.connector import Error

class MySQLQuery:
    def __init__(self, host, user, password, database):
        """初始化数据库连接"""
        self.host = host
        self.user = user
        self.password = password
        self.database = database
        self.connection = None

    def connect(self):
        """建立数据库连接"""
        try:
            self.connection = mysql.connector.connect(
                host=self.host,
                user=self.user,
                password=self.password,
                database=self.database
            )
            if self.connection.is_connected():
                print("✅ 成功连接到数据库")
        except Error as e:
            print(f"❌ 连接失败: {e}")
            self.connection = None

    def close(self):
        """关闭数据库连接"""
        if self.connection and self.connection.is_connected():
            self.connection.close()
            print("✅ 数据库连接已关闭")

    def execute_query(self, query, params=None):
        """执行单条 SQL 语句（INSERT/UPDATE/DELETE）"""
        if not self.connection or not self.connection.is_connected():
            print("❌ 数据库未连接")
            return None

        try:
            with self.connection.cursor(dictionary=True) as cursor:
                cursor.execute(query, params or ())
                self.connection.commit()
        except Error as e:
            print(f"❌ SQL 执行失败: {e}")
            self.connection.rollback()

    def fetch_all(self, query, params=None):
        """执行查询并返回所有结果"""
        if not self.connection or not self.connection.is_connected():
            print("❌ 数据库未连接")
            return None

        try:
            with self.connection.cursor(dictionary=True) as cursor:
                cursor.execute(query, params or ())
                return cursor.fetchall()  # 确保读取所有数据
        except Error as e:
            print(f"❌ 查询失败: {e}")
            return None

    def fetch_one(self, query, params=None):
        """执行查询并返回单条结果"""
        if not self.connection or not self.connection.is_connected():
            print("❌ 数据库未连接")
            return None

        try:
            with self.connection.cursor(dictionary=True) as cursor:
                cursor.execute(query, params or ())
                return cursor.fetchone()  # 读取单条数据
        except Error as e:
            print(f"❌ 查询失败: {e}")
            return None

    def insert_data(self, table, data):
        """插入数据（字典形式）"""
        if not self.connection or not self.connection.is_connected():
            print("❌ 数据库未连接")
            return None

        placeholders = ", ".join(["%s"] * len(data))
        columns = ", ".join(data.keys())
        query = f"INSERT INTO {table} ({columns}) VALUES ({placeholders})"
        self.execute_query(query, tuple(data.values()))

    def update_data(self, table, data, where):
        """更新数据（字典形式）"""
        if not self.connection or not self.connection.is_connected():
            print("❌ 数据库未连接")
            return None

        set_clause = ", ".join([f"{key}=%s" for key in data.keys()])
        where_clause = " AND ".join([f"{key}=%s" for key in where.keys()])
        query = f"UPDATE {table} SET {set_clause} WHERE {where_clause}"
        self.execute_query(query, tuple(data.values()) + tuple(where.values()))

    def delete_data(self, table, where):
        """删除数据"""
        if not self.connection or not self.connection.is_connected():
            print("❌ 数据库未连接")
            return None

        where_clause = " AND ".join([f"{key}=%s" for key in where.keys()])
        query = f"DELETE FROM {table} WHERE {where_clause}"
        self.execute_query(query, tuple(where.values()))
